package cn.yunhe.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.yunhe.biz.IProvienceMessageBiz;
import cn.yunhe.biz.IUniverstityBiz;
import cn.yunhe.biz.ProvienceMessageBizImpl;
import cn.yunhe.biz.UniverstityBizImpl;
import cn.yunhe.entity.Enroll_in_province;
import cn.yunhe.entity.Province;
import cn.yunhe.entity.University;

import cn.yunhe.util.DButil;


public class ScoreDaoImpl implements IScoreDao {

	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet res = null;
	String sql = null;
	int re = 0;
	List list = new ArrayList();
	
	public List<University> SearchProvince(int score,int pid,int wenorli) {
		
		try {
			conn = DButil.getCon();
			sql = "select u.*  from ENROLL_IN_PROVINCE e join UNIVERSITYS u on e.universityid = u.universityid where LOWEST<=? and UNIVERSITYID = ? and WENORLI = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, score);
			ps.setInt(2, pid);
			ps.setInt(3, wenorli);
			res = ps.executeQuery();
			while(res.next()){
				University us = new University();
				us.setUniversityid(res.getInt(1));
				us.setUname(res.getString(2));
				us.setIsnef(res.getInt(3));
				us.setIstoo(res.getInt(4));
				us.setContacts(res.getString(5));
				us.setUrl(res.getString(6));
				list.add(us);
				
			}
			
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			DButil.close(conn, ps, res);
		}
		
		return list;
	}
	
	public List<Enroll_in_province> search(String sql,Object[] pramas){
		conn=DButil.getCon();
		try {
			ps=conn.prepareStatement(sql);
			res=ps.executeQuery();
			while(res.next()){
				Enroll_in_province eip = new Enroll_in_province();
				eip.setEid(res.getInt("eid"));
				eip.setMajors(res.getString("majors"));
				eip.setEnroll_number(res.getInt("enroll_number"));
				eip.setAverage(res.getInt("average"));
				eip.setLowest(res.getInt("lowest"));
				eip.setWenOrLi(res.getInt("wenorli"));
				
				int universityid =res.getInt("UNIVERSITYID");
				IUniverstityBiz iub = new UniverstityBizImpl();
				eip.setUniversity(iub.selectUniById(universityid));
				
				int pid = res.getInt("pid");
				IProvienceMessageBiz ipmb = new ProvienceMessageBizImpl();
				eip.setProviencemessage(ipmb.selectProById(pid));
				
				list.add(eip);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DButil.close(conn, ps, res);
		}
		
		return list;
		
	}
	
	
	@Override
	public List<Enroll_in_province> getAll() {
		String sql = "select * from ENROLL_IN_PROVINCE";
		return search(sql,null);
	}

	@Override

	public List<Province> SearchAll() {
		
		try {
			conn = DButil.getCon();
			sql="select t.id,t.province from PROVINCES t";
			ps = conn.prepareStatement(sql);
			res = ps.executeQuery();
			while(res.next()){
				Province pv = new Province();
				pv.setId(res.getInt(1));
				pv.setProvince(res.getString(2));
				list.add(pv);
			}
			
		} catch (SQLException e) {


			e.printStackTrace();
		}finally{
			DButil.close(conn, ps, res);
		}
		
		
		return list;
	}

	public int add(String major, int wenorli, int e_pro, int e_uni, int e_num, int e_avg, int e_low) {
		String sql = "insert into ENROLL_IN_PROVINCE values(seq_sys_majors.nextval,?,?,?,?,?,?,?)";
		Object[] pramas={major,e_num,e_avg,e_low,e_uni,e_pro,wenorli};
		return DButil.insertOrDelOrUpdate(sql, pramas);
	}

	@Override
	public int update(String major, int wenorli, int e_pro, int e_uni, int e_num, int e_avg, int e_low, int e_id) {
		String sql = "update ENROLL_IN_PROVINCE set MAJORS=?,ENROLL_NUMBER=?,AVERAGE=?,LOWEST=?,UNIVERSITYID=?,PID=?,WENORLI=?where EID=?";
		Object[] pramas={major,e_num,e_avg,e_low,e_uni,e_pro,wenorli,e_id};
		return DButil.insertOrDelOrUpdate(sql, pramas);
	}

	@Override
	public int del(int e_id) {
		String sql ="delete from ENROLL_IN_PROVINCE where eid=?";
		Object[] pramas={e_id};
		return DButil.insertOrDelOrUpdate(sql, pramas);

	}

}
